USE[{0}]

select dbschemas.[name] as 'Schema', 
	dbtables.[name] as 'Table', 
	dbindexes.[name] as 'Index',
	indexstats.avg_fragmentation_in_percent as 'AvgFragmentation',
	cast(indexstats.page_count as int) as 'PageCount',	
	case 
	   WHEN indexstats.avg_fragmentation_in_percent <= 30  and  indexstats.avg_fragmentation_in_percent > 5
		 AND indexstats.page_count >= 500
		  THEN 'The index should be ''Reorganized''' 
	   WHEN indexstats.avg_fragmentation_in_percent >  30 
		 AND indexstats.page_count >= 500
		  THEN 'The index should be ''Rebuilt''' 
	 WHEN indexstats.page_count < 500 then 'No action required' 
	 END as 'Suggestion'
from sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) as indexstats
	INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
	INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
	INNER JOIN sys.indexes as dbindexes on dbindexes.[object_id] = indexstats.[object_id]
		AND indexstats.index_id = dbindexes.index_id
    inner join sys.databases db on db.database_id = indexstats.database_id
where db.name = '{0}' and dbindexes.index_id > 0 -- no heaps
order by indexstats.avg_fragmentation_in_percent desc